package com.eorchis.module.usermessagemanage.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.eorchis.constants.SystemConstants;
import com.eorchis.core.basedao.dao.IDaoSupport.QueryStringType;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.query.condition.IQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.module.usermessagemanage.dao.IUserMessageManageDao;
import com.eorchis.module.usermessagemanage.domain.ExportUserMessageManageBean;
import com.eorchis.module.usermessagemanage.domain.UserCapacityBean;
import com.eorchis.module.usermessagemanage.domain.UserMessageManageBean;
import com.eorchis.module.usermessagemanage.ui.commond.UserMessageManageQueryCommond;
import com.eorchis.utils.utils.PropertyUtil;


@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.usermessagemanage.dao.impl.UserMessageManageDaoImpl")
public class UserMessageManageDaoImpl extends HibernateAbstractBaseDao implements IUserMessageManageDao {

	@Override
	public List<UserMessageManageBean> findUserList(UserMessageManageQueryCommond queryCommond) throws Exception {
		
		Map<String, Object> values = new HashMap<String, Object>();
		//StringBuffer countSql = new StringBuffer("select count(1) ");
		StringBuffer querySql = new StringBuffer("select u.LOGIN_ID as \"loginId\", u.BIRTHDAY as \"birthDay\", "
				+ "udu.DEPUSER_ID as \"depUserID\", u.USERID as \"userID\", u.USER_NAME as \"userName\", "
				+ "ub.DATA_NAME as \"sexName\",ud1.DEPNAME as \"deptPath\", u.ACTIVE_STATE as \"activeState\","
				+ "u.EXAM_STATE as \"examState\", u.SHIGUAN as \"shiGuan\", u.CHISEL_STATE as \"chiselState\","
				+ "u.DUTY_REMARK as \"duty\", ub2.DATA_NAME as \"dutyLevel\", u.HIERARCHY as \"hierarchy\", "
				+ "u.AVAILABILITY_STATE as \"availabilityState\", utu.UPDATE_STATE as \"updateState\", "
				+ "udu.LAST_OPERATE_TIME as \"lastOperateTime\",ud1.TREEPATH as \"treepath\" ");
		querySql.append(" from " + SystemConstants.SYSTEM_CODE + " UUM_DEPARTMENT_USER udu "
				+ " inner join " + SystemConstants.SYSTEM_CODE + " UUM_USER u on udu.USERID=u.USERID "
				+ " left outer join " + SystemConstants.SYSTEM_CODE + " UUM_TEMP_USER utu on u.USERID=utu.USERID "
				+ " left outer join " + SystemConstants.SYSTEM_CODE + " UUM_BASEDATA ub on u.SEX_CODE=ub.DATA_ID "
				+ " left outer join " + SystemConstants.SYSTEM_CODE + " UUM_BASEDATA ub2 on u.DUTY_LEVEL_CODE=ub2.DATA_ID "
				+ " left outer join " + SystemConstants.SYSTEM_CODE + " UUM_DEPARTMENT ud1 on udu.DEPID=ud1.DEPID "
				+ " left outer join " + SystemConstants.SYSTEM_CODE + " UUM_DEPARTMENT ud2 on udu.OLD_DEPTID=ud2.DEPID," + SystemConstants.SYSTEM_CODE + "view_user_age vua "
				+ " where udu.USERID=vua.USERID and (ud1.TREEPATH like :treepath) "
				+ " and (u.LOGIN_ID not like '9%') and u.USER_NAME<>'通讯员' "
				+ " and (u.USER_NAME not like '%管理员') " );
		values.put("treepath", queryCommond.getTreepath() + "%");
		//身份
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchStatusCodes())) {
			querySql.append(" and (u.USERID in (select uucl.UUCL_USERID from " + SystemConstants.SYSTEM_CODE + " UUM_USER_CAPACITY_LINK uucl where uucl.UUCL_DATA_CODE in (:statusCodes))) ");
			values.put("statusCodes", queryCommond.getSearchStatusCodes());
		}
		//登录账号
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginId())) {
			querySql.append(" and u.LOGIN_ID=:loginId ");
			values.put("loginId", queryCommond.getSearchLoginId());
		}
		//职级
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchrankCode())) {
			querySql.append(" and ub2.DATA_ID = :rankCode");
			values.put("rankCode", queryCommond.getSearchrankCode());
		}
		//学生姓名
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			if (queryCommond.getSearchUserName() != "") {
				querySql.append(" and u.USER_NAME like :userName");
				values.put("userName","%" + queryCommond.getSearchUserName() + "%");
			}
		}
		//市管干部
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSiGuan())) {
			querySql.append(" and u.SHIGUAN = :shiGuan");
			values.put("shiGuan", queryCommond.getSearchSiGuan());
		}
		//增加排序
		SortInfoBean sortInfo = queryCommond.getSortInfos();
		if (sortInfo != null && PropertyUtil.objectNotEmpty(sortInfo)){
//			SortInfoBean sortInfo = sortInfoList.get(0);
			if (PropertyUtil.objectNotEmpty(sortInfo.getProperty())){
				querySql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			}
			querySql.append(" , u.USERID ");
		}else {
			querySql.append(" order by ub2.DATA_SEQ, u.USERID ");
		}
		IConditionBuilder conditionBuilder = new DefaultQueryConditionBuilder();
		conditionBuilder.setBaseQueryString(querySql.toString());
		conditionBuilder.setQueryStringType(QueryStringType.SQL);
		conditionBuilder.setResultObject(UserMessageManageBean.class);
		IQueryCondition queryCondition = conditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<UserMessageManageBean> userList = new ArrayList<UserMessageManageBean>();
		if (queryCommond.getPage() == -1) {
			userList = (List<UserMessageManageBean>)this.findList(queryCondition);
		}else {
			Long count = this.count(queryCondition);
			int firstResult = queryCommond.calculate(count);
			userList = (List<UserMessageManageBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return userList;
	}

	
	
	/**
	 * 修改查询
	 * @param userID
	 * @return
	 */
	public UserMessageManageBean getUserMessageManageBean(Integer userID) {
		
		StringBuffer sql = new StringBuffer("SELECT u.USERID as \"userID\","
				+ "u.OFFICEHOLDING_DATE as \"heldOfficeDate\", u.LOGIN_ID as \"loginId\"," 
				+ "u.PAPER_NUM AS \"paperNum\" FROM " + SystemConstants.SYSTEM_CODE + "UUM_USER u WHERE u.USERID = :userId");
		Map<String, Object> values = new HashMap<String, Object>();
		values.put("userId", userID);
		UserMessageManageBean user = (UserMessageManageBean)(this.executeFind(QueryStringType.SQL, sql.toString(), values,UserMessageManageBean.class).get(0));
		
		return user;
	}
	
	
	public void updateUserMessageManage(UserMessageManageQueryCommond queryCommond) {
		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("update " + SystemConstants.SYSTEM_CODE + "UUM_USER u set ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getPaperNum())) {
			sql.append(" u.PAPER_NUM = :paperNum");
			values.put("paperNum", queryCommond.getPaperNum());
		}else {
			sql.append(" u.PAPER_NUM = :paperNum");
			values.put("paperNum", "");
			
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getHeldofficeDate())) {
			sql.append(" ,u.OFFICEHOLDING_DATE = :heldOfficeDate");
			values.put("heldOfficeDate", queryCommond.getHeldofficeDate());
		}
//		if (PropertyUtil.objectNotEmpty(queryCommond.getLoginId())) {
//			sql.append(" ,u.LOGIN_ID = :loginId ");
//			values.put("loginId", queryCommond.getLoginId());
//		}
		sql.append(" where u.USERID = :userId");
		values.put("userId", queryCommond.getUserID());
		this.executeUpdate(QueryStringType.SQL, sql.toString(), values);
	}
	
	
	
	/**
	 * 导出list
	 * @param queryCommond
	 * @return
	 */
	public List<ExportUserMessageManageBean> export(UserMessageManageQueryCommond queryCommond){
		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer querySql = new StringBuffer("SELECT u.USER_NAME AS \"userName\",concat(u.LOGIN_ID,\"\") AS \"loginId\",b4.DATA_NAME AS \"sexName\",u.BIRTHDAY AS \"birthday\","
						+ "u.PHONE AS \"phone\",u.MOBILE_TELEPHONE AS \"mobelPhone\",u.EMAIL AS \"email\",b6.DATA_NAME AS \"folkName\"," 
						+ "	u.PAPER_NUM AS \"IDNumber\",b7.DATA_NAME AS \"political\",b8.DATA_NAME AS \"education\",d3.DEPNAME AS \"unit\"," 
						+ "	b5.DATA_NAME AS \"dutyLevel\",u.DUTY_REMARK AS \"duty\",u.IS_BUREAU_RESERVE AS \"isBureauBackUpString\",u.ZUGONG AS \"isZuGong\"," 
						+ "	u.EXAM_STATE AS \"examState\",u.AVAILABILITY_STATE AS \"registrationState\" "
						+ " FROM " + SystemConstants.SYSTEM_CODE + "UUM_DEPARTMENT_USER d1 "
						+ "INNER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_USER u ON d1.USERID = u.USERID"
						+ " LEFT OUTER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_TEMP_USER t2 ON u.USERID = t2.USERID"
						+ " LEFT OUTER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_BASEDATA b4 ON u.SEX_CODE = b4.DATA_ID"
						+ " LEFT OUTER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_BASEDATA b5 ON u.DUTY_LEVEL_CODE = b5.DATA_ID"
						+ " LEFT OUTER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_BASEDATA b6 ON u.FOLK_CODE = b6.DATA_ID"
						+ " LEFT OUTER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_BASEDATA b7 ON u.POLITICES_CODE = b7.DATA_ID"
						+ " LEFT OUTER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_BASEDATA b8 ON u.DEGREE_CODE = b8.DATA_ID "
						+ " LEFT OUTER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_DEPARTMENT d3 ON d1.DEPID = d3.DEPID"
						+ " LEFT OUTER JOIN " + SystemConstants.SYSTEM_CODE + "UUM_DEPARTMENT d9 ON d1.OLD_DEPTID = d9.DEPID,"
						+ SystemConstants.SYSTEM_CODE + "view_user_age u10 WHERE d1.USERID = u10.USERID AND (d3.TREEPATH LIKE :treepath)"
						+ " AND (u.LOGIN_ID NOT LIKE '9%') AND u.USER_NAME <> '通讯员' "
						+ " AND (u.USER_NAME NOT LIKE '%管理员') ");
		
				values.put("treepath", queryCommond.getTreepath() + "%");
		
				//身份
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchStatusCodes()) && PropertyUtil.objectNotEmpty(queryCommond.getSearchStatusCodes()[0])) {
					querySql.append(" and (u.USERID in (select uucl.UUCL_USERID from " + SystemConstants.SYSTEM_CODE + " UUM_USER_CAPACITY_LINK uucl where uucl.UUCL_DATA_ID in (:statusCodes))) ");
					values.put("statusCodes", queryCommond.getSearchStatusCodes());
				}
				//登录账号
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginId())) {
					querySql.append(" and u.LOGIN_ID=:loginId ");
					values.put("loginId", queryCommond.getSearchLoginId());
				}
				//职级
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchrankCode())) {
					querySql.append(" and b5.DATA_ID = :rankCode");
					values.put("rankCode", queryCommond.getSearchrankCode());
				}
				//学生姓名
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
					querySql.append(" and u.USER_NAME like :userName");
					values.put("userName","%" + queryCommond.getSearchUserName() + "%");
				}
				//市管干部
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSiGuan())) {
					querySql.append(" and u.SHIGUAN = :shiGuan");
					values.put("shiGuan", queryCommond.getSearchSiGuan());
				}
		
				querySql.append(" ORDER BY b5.DATA_SEQ,u.USERID ");
				
				
				List<ExportUserMessageManageBean> userList = (List<ExportUserMessageManageBean>)(this.executeFind(QueryStringType.SQL, querySql.toString(), values,ExportUserMessageManageBean.class));		
				return userList;
	}
	
	
	public List<UserCapacityBean> findCodes(UserMessageManageQueryCommond queryCommond) throws Exception{
		StringBuffer sql = new StringBuffer("SELECT uucl.UUCL_ID AS \"uuclId\" ,uucl.UUCL_DATA_CODE AS \"dataCode\","
				+ "uucl.UUCL_USERID AS \"userId\" FROM " + SystemConstants.SYSTEM_CODE +" UUM_USER_CAPACITY_LINK uucl, " + SystemConstants.SYSTEM_CODE +"UUM_BASEDATA ub"
				+ " WHERE 1 = 1 and uucl.UUCL_USERID in " +getStringFromList(queryCommond.getUserId())
//				+ " WHERE 1 = 1 and uucl.UUCL_USERID :userId "
				+ " and ub.DATA_ID = uucl.UUCL_DATA_ID order by ub.DATA_CODE");
		
		Map<String, Object> values = new HashMap<String, Object>();
		List<UserCapacityBean> userCapacitylist = (List<UserCapacityBean>)(this.executeFind(QueryStringType.SQL, sql.toString(), values,UserCapacityBean.class));
		
		return userCapacitylist;
		
	}
	
	private String getStringFromList(List<Integer> userIDs)throws Exception {
		if(userIDs!=null&&userIDs.size()>0){
			String userIDStrs="(";
			for(Integer id:userIDs){
				if(id!=null&&!new Integer(0).equals(id)){
					userIDStrs+=id+",";
				}
			}
			if(userIDStrs.length()>1){
				userIDStrs=userIDStrs.substring(0, userIDStrs.length()-1)+")";
			}
			return userIDStrs;
		}
		return null;
	}
	
	
	public String findPaperNum(UserMessageManageQueryCommond queryCommond) throws Exception{
		StringBuffer sb = new StringBuffer("select u.PAPER_NUM from "+ SystemConstants.SYSTEM_CODE +"UUM_USER u where u.PAPER_NUM = :prperNum and u.LOGIN_ID != :loginId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("prperNum", queryCommond.getPaperNum());
		params.put("loginId", queryCommond.getLoginId());
		List<?> list = this.executeFind(QueryStringType.SQL, sb.toString(), params);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0))) {
			return list.get(0).toString();
		}
		return null;
	}
	
	
}
